package dao;

/**
 *
 * @author Marlon
 */
import baperjakat.Koneksi;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import model.Kriteria;

public class KriteriaDao {
    private Connection c;
    private PreparedStatement p;
    private Statement s;
    
    public KriteriaDao(){
        c = Koneksi.getKoneksi();
    }

    public void insert(Kriteria k) throws SQLException {
        p = c.prepareStatement("INSERT INTO kriteria (bobot, nilai, kriteria, idjabatan) VALUES (?, ?, ?, ?)");
        simpan(k, p);
    }
    
    public void update(Kriteria k) throws SQLException {
        p = c.prepareStatement("UPDATE kriteria SET bobot = ?, nilai = ?, kriteria = ? WHERE idjabatan = ?");
        simpan(k, p);
    }

    public void delete(Kriteria k) throws SQLException {
        p = c.prepareStatement("DELETE FROM kriteria WHERE kriteria = ? ");
            
        p.setString(1,k.getKriteria());
        p.executeUpdate();
        p.close();
    }
    
    public void simpan(Kriteria k, PreparedStatement p) throws SQLException{
        
        p.setString(1,k.getBobot());
        p.setString(2,k.getNilai());
        p.setString(3,k.getKriteria());
        p.setString(4,k.getId());
        
        p.executeUpdate();
        p.close();
    }
    
    public Kriteria getById(String id) throws SQLException {
        s = c.createStatement();
        String sql = "SELECT FROM kriteria WHERE idkriteria = ?";
        ResultSet r = s.executeQuery(sql);
        
        if(!r.next())
            return null;

        Kriteria k = new Kriteria();
        k.setKriteria(r.getString("kriteria"));
        k.setBobot(r.getString("bobot"));
        k.setNilai(r.getString("nilai"));
        
        r.close();
        s.close();

        return k;
    }
    
    public Kriteria getByBobot(String bobot) throws SQLException{
        s = c.createStatement();
        String sql = "Select kriteria where bobot = ?";
        ResultSet r = s.executeQuery(sql);
        
        if(!r.next())
            return null;

        Kriteria k = new Kriteria();
        k.setBobot(r.getString("bobot"));
        
        r.close();
        s.close();
        
        return k;
    }
    
     public List<Kriteria> getAll() throws SQLException {
        List<Kriteria> listKriteria = new ArrayList<>();
        
        s = c.createStatement();
        String sql = "SELECT * FROM kriteria";
        ResultSet r = s.executeQuery(sql);
        
        Kriteria k = null;
        while(r.next()) {
            k = new Kriteria();
            k.setId(r.getString("idkriteria"));
            k.setKriteria(r.getString("kriteria"));
            k.setBobot(r.getString("bobot"));
            k.setNilai(r.getString("nilai"));
            
            listKriteria.add(k);
        }
        
        r.close();
        s.close();

        return listKriteria;
    }
     
     public List<Kriteria> getByJabatan(String jabatan) throws SQLException {
        List<Kriteria> listKriteria = new ArrayList<>();
        
        s = c.createStatement();
        String sql = "Select k.idkriteria, k.kriteria, k.bobot, k.nilai from kriteria k inner join jabatan j on j.idjabatan = k.idjabatan where j.namajabatan = '" + jabatan + "'";
        ResultSet r = s.executeQuery(sql);
        
        Kriteria k = null;
        while(r.next()){
            k = new Kriteria();
            k.setId(r.getString("idkriteria"));
            k.setKriteria(r.getString("kriteria"));
            k.setBobot(r.getString("bobot"));
            k.setNilai(r.getString("nilai"));
            
            listKriteria.add(k);
        }
        
        r.close();
        s.close();

        return listKriteria;
     }
}
